﻿using System.Data;
using System.Data.SqlClient;
using EntitiesTM;

namespace DataAccess
{
    public class DaProject
    {
        public DataTable selAllProject(bool isEnable, string companyid)
        {
            string strCommandText = "selAllProject"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@IsEnable", isEnable);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public DataTable selAllProjectReport(string companyid)
        {
            string strCommandText = "selAllProjectReport"+companyid;
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, null);
        }
        public DataTable selProjectRegister(string email, string companyid)
        {
            string strCommandText = "selProjectRegister"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@Email", email);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public DataTable selProjectRegister2(string email, string companyid)
        {
            string strCommandText = "selProjectRegister2Tmp"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@Email", email);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public DataTable selProjectRegisterTmp(string email, int level, int parentID, string companyid)
        {
            string strCommandText = "selProjectRegisterTmp"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[3];
            paraLocal[0] = new SqlParameter("@Email", email);
            paraLocal[1] = new SqlParameter("@Level", level);
            paraLocal[2] = new SqlParameter("@ParentID", parentID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public DataTable selProjectRegisterTree(string email, int parentID, string companyid)
        {
            string strCommandText = "selProjectRegisterTree"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[2];
            paraLocal[0] = new SqlParameter("@Email", email);
            paraLocal[1] = new SqlParameter("@ParentID", parentID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public DataTable selProjectByParentID(int parentID, int exceptID, string companyid)
        {
            string strCommandText = "selProjectByParentID"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[2];
            paraLocal[0] = new SqlParameter("@ParentID", parentID);
            paraLocal[1] = new SqlParameter("@ExceptID", exceptID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public DataTable selProjectByParentIDSort(int parentID, int exceptID, string companyid)
        {
            string strCommandText = "selProjectByParentIDSort"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[2];
            paraLocal[0] = new SqlParameter("@ParentID", parentID);
            paraLocal[1] = new SqlParameter("@ExceptID", exceptID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public DataTable selProjectByID(int projectID, string companyid)
        {
            string strCommandText = "selProjectByID"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", projectID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public DataTable selProjectByIDTree(int projectID, string companyid)
        {
            string strCommandText = "selProjectByIDTree"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", projectID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public DataTable selTopProjectID(string companyid)
        {
            string strCommandText = "selTopProjectID"+companyid;
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, null);
        }

        public DataTable selStaffMemberProject(int projectID, string companyid)
        {
            string strCommandText = "selStaffMemberProject"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", projectID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public DataTable selStaffMemberProjectAdd(int projectID, string companyid)
        {
            string strCommandText = "selStaffMemberProjectAdd"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", projectID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public DataTable selTimeUser(int projectID, string companyid)
        {
            string strCommandText = "selTimeUser"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", projectID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void insProject(EnProject project, string companyid)
        {
            string strCommandText = "insProject"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[10];
            paraLocal[0] = new SqlParameter("@ProjectName", project.projectName);
            paraLocal[1] = new SqlParameter("@ProjectDescription", project.projectDescription);
            paraLocal[2] = new SqlParameter("@ProjectClientID", project.projectClientID);
            paraLocal[3] = new SqlParameter("@ProjectStartDate", project.projectStartDate);
            paraLocal[4] = new SqlParameter("@ProjectEndDate", project.projectEndDate);
            paraLocal[5] = new SqlParameter("@ProjectEnable", project.projectEnable);
            paraLocal[6] = new SqlParameter("@ProjectEnableMember", project.projectEnableMember);
            paraLocal[7] = new SqlParameter("@ProjectManager", project.projectManager);
            paraLocal[8] = new SqlParameter("@ProjectParentID", project.projectParentID);
            paraLocal[9] = new SqlParameter("@ProjectCode", project.projectCode);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void updProject(EnProject project, string companyid)
        {
            string strCommandText = "updProject"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[9];
            paraLocal[0] = new SqlParameter("@ProjectName", project.projectName);
            paraLocal[1] = new SqlParameter("@ProjectDescription", project.projectDescription);
            paraLocal[2] = new SqlParameter("@ProjectClientID", project.projectClientID);
            paraLocal[3] = new SqlParameter("@ProjectStartDate", project.projectStartDate);
            paraLocal[4] = new SqlParameter("@ProjectEndDate", project.projectEndDate);
            paraLocal[5] = new SqlParameter("@ProjectEnable", project.projectEnable);
            paraLocal[6] = new SqlParameter("@ProjectEnableMember", project.projectEnableMember);
            paraLocal[7] = new SqlParameter("@ProjectManager", project.projectManager);
            paraLocal[8] = new SqlParameter("@ProjectID", project.projectID);
            //paraLocal[9] = new SqlParameter("@ProjectParentID", project.projectParentID);
            //paraLocal[10] = new SqlParameter("@ProjectID", project.projectCode);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void updProjectTree(EnProject project, string companyid)
        {
            string strCommandText = "updProjectTree"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[5];
            paraLocal[0] = new SqlParameter("@ProjectName", project.projectName);
            paraLocal[1] = new SqlParameter("@ProjectDescription", project.projectDescription);
            paraLocal[2] = new SqlParameter("@ProjectID", project.projectID);
            paraLocal[3] = new SqlParameter("@ProjectParentID", project.projectParentID);
            paraLocal[4] = new SqlParameter("@ProjectCode", project.projectCode);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public void updProjectTreeChild(int projectID, string companyid)
        {
            string strCommandText = "updProjectTreeChild"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", projectID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public void delProject(EnProject project, string companyid)
        {
            string strCommandText = "delProject"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", project.projectID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void delProjectChild(EnProject project, string companyid)
        {
            string strCommandText = "delProjectChild"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", project.projectID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void insSP(EnSP sp, string companyid)
        {
            string strCommandText = "insSP"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[2];
            paraLocal[0] = new SqlParameter("@SPStaffID", sp.spStaffID);
            paraLocal[1] = new SqlParameter("@SPCostPerHour", sp.spCostPerHour);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void delActivityProject(EnProject project, string companyid)
        {
            string strCommandText = "delActivityProject"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", project.projectID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void delSPProject(EnProject project, string companyid)
        {
            string strCommandText = "delSPProject"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", project.projectID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }


        public void delSAProject(EnProject project, string companyid)
        {
            string strCommandText = "delSAProject"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", project.projectID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public DataTable selAllTypeTime(string companyid)
        {
            string strCommandText = "selAllTypeTime"+companyid;
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, null);
        }

        public void insActivity(EnActivity activity, string companyid)
        {
            string strCommandText = "insActivity"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[3];
            paraLocal[0] = new SqlParameter("@ActivityName", activity.activityName);
            paraLocal[1] = new SqlParameter("@ActivityEstimateTime", activity.activityEstimateTime);
            paraLocal[2] = new SqlParameter("@ActivityProjectID", activity.activityProjectID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public void insActivity2(EnActivity activity, string companyid)
        {
            string strCommandText = "insActivityTmp"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[4];
            paraLocal[0] = new SqlParameter("@ActivityName", activity.activityName);
            paraLocal[1] = new SqlParameter("@ActivityEstimateTime", activity.activityEstimateTime);
            paraLocal[2] = new SqlParameter("@ActivityProjectID", activity.activityProjectID);
            paraLocal[3] = new SqlParameter("@ActivityCode", activity.activityCode);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void upLevel(EnActivity activity, string companyid)
        {
            string strCommandText = "upLevel"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ActivityID", activity.activityID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void downLevel(EnActivity activity, string companyid)
        {
            string strCommandText = "downLevel"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ActivityID", activity.activityID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void insProjectMember(EnSP sp, string companyid)
        {
            string strCommandText = "insProjectMember"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[3];
            paraLocal[0] = new SqlParameter("@SPStaffID", sp.spStaffID);
            paraLocal[1] = new SqlParameter("@SPProjectID", sp.spProjectID);
            paraLocal[2] = new SqlParameter("@SPCostPerHour", sp.spCostPerHour);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void updProjectMember(EnSP sp, string companyid)
        {
            string strCommandText = "updProjectMember"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[3];
            paraLocal[0] = new SqlParameter("@SPStaffID", sp.spStaffID);
            paraLocal[1] = new SqlParameter("@SPProjectID", sp.spProjectID);
            paraLocal[2] = new SqlParameter("@SPCostPerHour", sp.spCostPerHour);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void delProjectMember(EnSP sp, string companyid)
        {
            string strCommandText = "delProjectMember"+companyid;

            SqlParameter[] paraLocal = new SqlParameter[2];
            paraLocal[0] = new SqlParameter("@SPStaffID", sp.spStaffID);
            paraLocal[1] = new SqlParameter("@SPProjectID", sp.spProjectID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public DataTable selProjectExceptID(EnProject project, string companyid)
        {
            string strCommandText = "selProjectExceptID"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", project.projectID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public DataTable selProjectExceptIDNew(EnProject project, string companyid)
        {
            string strCommandText = "selProjectExceptIDNew"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", project.projectID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public DataTable selActivityByProjectExceptID(EnProject project, string companyid)
        {
            string strCommandText = "selActivityByProjectExceptID"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@ProjectID", project.projectID);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

    }
}
